package com.fenfen.novel.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

import com.fenfen.novel.utils.CommonUtil;

import java.util.ArrayList;
import java.util.List;

public class NovelDBProvider extends SQLiteOpenHelper {
  private static String name = "novel.db";
  private static int version = 2;

  private static NovelDBProvider instance;
  private Context mContext;

  public static NovelDBProvider getInstance(Context context) {
    if (instance == null) {
      synchronized (NovelDBProvider.class) {
        if (instance == null) {
          instance = new NovelDBProvider(context);
        }
      }
    }
    return instance;
  }

  private NovelDBProvider(Context context) {
    this(context, name, null, version);
    this.mContext = context;
  }

  private NovelDBProvider(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    String createNovelRecordSql = "CREATE TABLE " + NovelTable.Table_NovelReadRecord
        + "("
        + NovelTable.NovelReadRecord._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        + NovelTable.NovelReadRecord.NOVEL_ID + " INTEGER,"
        + NovelTable.NovelReadRecord.NOVEL_NAME + " TEXT,"
        + NovelTable.NovelReadRecord.NOVEL_IMG + " TEXT,"
        + NovelTable.NovelReadRecord.CHAPTER_ID + " INTEGER,"
        + NovelTable.NovelReadRecord.CHAPTER_ORDER + " INTEGER,"
        + NovelTable.NovelReadRecord.CHAPTER_COUNT + " INTEGER,"
        + NovelTable.NovelReadRecord.CREATE_TIME + " TEXT,"
        + NovelTable.NovelReadRecord.UPDATE_TIME + " TEXT"
        + ")";
    String createNovelLogSql = "CREATE TABLE " + NovelTable.Table_NovelReadLog
        + "("
        + NovelTable.NovelReadLog._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        + NovelTable.NovelReadLog.NOVEL_ID + " INTEGER,"
        + NovelTable.NovelReadLog.CHAPTER_ID + " INTEGER,"
        + NovelTable.NovelReadLog.CHAPTER_ORDER + " INTEGER,"
        + NovelTable.NovelReadLog.CREATE_TIME + " TEXT"
        + ")";
    db.execSQL(createNovelRecordSql);
    db.execSQL(createNovelLogSql);
    createNovelDownTable(db);
  }

  private void createNovelDownTable(SQLiteDatabase db) {
    String createNovelDownSql = "CREATE TABLE " + NovelTable.Table_NovelDownRecord
        + "("
        + NovelTable.NovelDownRecord._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        + NovelTable.NovelDownRecord.NOVEL_ID + " INTEGER,"
        + NovelTable.NovelDownRecord.DOWN_STATUS + " TEXT,"
        + NovelTable.NovelDownRecord.CREATE_TIME + " TEXT,"
        + NovelTable.NovelDownRecord.UPDATE_TIME + " TEXT"
        + ")";
    db.execSQL(createNovelDownSql);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
      case 1:
        createNovelDownTable(db);
      default:
        break;
    }
  }

  public List<NovelReadRecord> getReadRecordsByPage(int page) {
    if (page <= 0) {
      page = 1;
    }
    List<NovelReadRecord> list = new ArrayList<>();
    SQLiteDatabase db = instance.getReadableDatabase();
    String limit = (page - 1) * CommonUtil.PAGE_SIZE + "," + CommonUtil.PAGE_SIZE;
    Cursor cursor = db.rawQuery("select r.*, d.down_status from novel_read_record r left join"
        + " novel_down_record d on r.novel_id = d.novel_id order by r.update_time  DESC  limit " + limit, null);
    if (cursor != null && cursor.moveToFirst()) {
      do {
        NovelReadRecord record = resoveNovelReadRecord(cursor);
        list.add(record);
      } while (cursor.moveToNext());
    }
    return list;
  }

  private NovelReadRecord resoveNovelReadRecord(Cursor cursor) {
    int novelId = getIntColumn(cursor, NovelTable.NovelReadRecord.NOVEL_ID);
    String novelName = getStringColumn(cursor, NovelTable.NovelReadRecord.NOVEL_NAME);
    String novelCover = getStringColumn(cursor, NovelTable.NovelReadRecord.NOVEL_IMG);
    int chapterId = getIntColumn(cursor, NovelTable.NovelReadRecord.CHAPTER_ID);
    int chapterOrder = getIntColumn(cursor, NovelTable.NovelReadRecord.CHAPTER_ORDER);
    int chapterCount = getIntColumn(cursor, NovelTable.NovelReadRecord.CHAPTER_COUNT);
    String createTime = getStringColumn(cursor, NovelTable.NovelReadRecord.CREATE_TIME);
    String updateTime = getStringColumn(cursor, NovelTable.NovelReadRecord.UPDATE_TIME);
    String downStatus = getStringColumn(cursor, NovelTable.NovelDownRecord.DOWN_STATUS);
    NovelReadRecord record = new NovelReadRecord();
    record.setNovelId(novelId);
    record.setNovelName(novelName);
    record.setNovelCover(novelCover);
    record.setChapterId(chapterId);
    record.setChapterOrder(chapterOrder);
    record.setChapterCount(chapterCount);
    record.setDownStatus(TextUtils.isEmpty(downStatus) ? CommonUtil.DOWN_STATUS.DEFAULT : downStatus);
    record.setCreate_time(CommonUtil.getDateTime(createTime));
    record.setUpdate_time(CommonUtil.getDateTime(updateTime));
    return record;
  }

  public NovelReadRecord getReadRecordByNovelId(int novelId) {
    SQLiteDatabase db = instance.getReadableDatabase();
    Cursor cursor = db.query(NovelTable.Table_NovelReadRecord, null, NovelTable.NovelReadRecord.NOVEL_ID + " = ?", new String[]{String.valueOf(novelId)}, null, null, NovelTable.NovelReadRecord.UPDATE_TIME + " desc");
    if (cursor != null && cursor.moveToFirst()) {
      return resoveNovelReadRecord(cursor);
    }
    return null;
  }

  public void saveReadRecord(NovelReadRecord record) {
    SQLiteDatabase db = instance.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(NovelTable.NovelReadRecord.NOVEL_ID, record.getNovelId());
    values.put(NovelTable.NovelReadRecord.NOVEL_NAME, record.getNovelName());
    values.put(NovelTable.NovelReadRecord.NOVEL_IMG, record.getNovelCover());
    values.put(NovelTable.NovelReadRecord.CHAPTER_ID, record.getChapterId());
    values.put(NovelTable.NovelReadRecord.CHAPTER_ORDER, record.getChapterOrder());
    values.put(NovelTable.NovelReadRecord.CHAPTER_COUNT, record.getChapterCount());
    values.put(NovelTable.NovelReadRecord.CREATE_TIME, CommonUtil.getDateTimeStr(record.getCreate_time()));
    values.put(NovelTable.NovelReadRecord.UPDATE_TIME, CommonUtil.getDateTimeStr(record.getUpdate_time()));
    db.insert(NovelTable.Table_NovelReadRecord, null, values);
  }

  public void updateReadRecordNovel(NovelReadRecord record) {
    SQLiteDatabase db = instance.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(NovelTable.NovelReadRecord.NOVEL_NAME, record.getNovelName());
    values.put(NovelTable.NovelReadRecord.NOVEL_IMG, record.getNovelCover());
    values.put(NovelTable.NovelReadRecord.UPDATE_TIME, CommonUtil.getDateTimeStr(record.getCreate_time()));
    db.update(NovelTable.Table_NovelReadRecord, values, NovelTable.NovelReadRecord.NOVEL_ID + " = ?",
        new String[]{String.valueOf(record.getNovelId())});
  }

  public void updateReadRecordChapter(NovelReadRecord record) {
    SQLiteDatabase db = instance.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(NovelTable.NovelReadRecord.CHAPTER_ID, record.getChapterId());
    values.put(NovelTable.NovelReadRecord.CHAPTER_ORDER, record.getChapterOrder());
//    values.put(NovelTable.NovelReadRecord.CHAPTER_COUNT, record.getChapterCount());
    values.put(NovelTable.NovelReadRecord.UPDATE_TIME, CommonUtil.getDateTimeStr(record.getCreate_time()));
    db.update(NovelTable.Table_NovelReadRecord, values, NovelTable.NovelReadRecord.NOVEL_ID + " = ?",
        new String[]{String.valueOf(record.getNovelId())});
  }

  public void updateReadRecordAll(NovelReadRecord record) {
    SQLiteDatabase db = instance.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(NovelTable.NovelReadRecord.NOVEL_NAME, record.getNovelName());
    values.put(NovelTable.NovelReadRecord.NOVEL_IMG, record.getNovelCover());
    values.put(NovelTable.NovelReadRecord.CHAPTER_ID, record.getChapterId());
    values.put(NovelTable.NovelReadRecord.CHAPTER_ORDER, record.getChapterOrder());
    values.put(NovelTable.NovelReadRecord.CHAPTER_COUNT, record.getChapterCount());
    values.put(NovelTable.NovelReadRecord.UPDATE_TIME, CommonUtil.getDateTimeStr(record.getCreate_time()));
    db.update(NovelTable.Table_NovelReadRecord, values, NovelTable.NovelReadRecord.NOVEL_ID + " = ?",
        new String[]{String.valueOf(record.getNovelId())});
  }

  public boolean deleteReadRecord(int novelId) {
    SQLiteDatabase db = instance.getWritableDatabase();
    int rowCount = db.delete(NovelTable.Table_NovelReadRecord, NovelTable.NovelReadRecord.NOVEL_ID + " = ?", new String[]{String.valueOf(novelId)});
    return rowCount != 0;
  }

  private int getIntColumn(Cursor cursor, String columnName) {
    if (cursor == null || TextUtils.isEmpty(columnName)) {
      return CommonUtil.NONE_INT_DATA;
    }
    int columnIndex = cursor.getColumnIndex(columnName);
    if (columnIndex == CommonUtil.NONE_INT_DATA) {
      return CommonUtil.NONE_INT_DATA;
    }
    return cursor.getInt(columnIndex);
  }

  private String getStringColumn(Cursor cursor, String columnName) {
    if (cursor == null || TextUtils.isEmpty(columnName)) {
      return null;
    }
    int columnIndex = cursor.getColumnIndex(columnName);
    if (columnIndex == CommonUtil.NONE_INT_DATA) {
      return null;
    }
    return cursor.getString(columnIndex);
  }

  public boolean isDownStatusExistByNovelId(int novelId) {
    SQLiteDatabase db = instance.getReadableDatabase();
    Cursor cursor = db.query(NovelTable.Table_NovelDownRecord, null, NovelTable.NovelDownRecord.NOVEL_ID + " = ?", new String[]{String.valueOf(novelId)}, null, null, NovelTable.NovelDownRecord.UPDATE_TIME + " desc");
    if (cursor != null && cursor.moveToFirst()) {
      cursor.close();
      return true;
    }
    return false;
  }

  public boolean saveDownStatus(NovelDownStatus status, NovelReadRecord record) {
    if (!isDownStatusExistByNovelId(status.getNovelId())) {
      SQLiteDatabase db = instance.getWritableDatabase();
      ContentValues values = new ContentValues();
      values.put(NovelTable.NovelDownRecord.NOVEL_ID, status.getNovelId());
      values.put(NovelTable.NovelDownRecord.DOWN_STATUS, status.getDownStatus());
      values.put(NovelTable.NovelDownRecord.CREATE_TIME, CommonUtil.getDateTimeStr(status.getCreate_time()));
      values.put(NovelTable.NovelDownRecord.UPDATE_TIME, CommonUtil.getDateTimeStr(status.getUpdate_time()));
      long id = db.insert(NovelTable.Table_NovelDownRecord, null, values);
      if (id == CommonUtil.NONE_INT_DATA) {
        return false;
      }
    }
    NovelReadRecord oldRecord = getReadRecordByNovelId(status.getNovelId());
    if (oldRecord == null) {
      saveReadRecord(record);
    }
    return true;
  }

  public void updateDownStatus(NovelDownStatus status) {
    SQLiteDatabase db = instance.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(NovelTable.NovelDownRecord.DOWN_STATUS, status.getDownStatus());
    values.put(NovelTable.NovelDownRecord.UPDATE_TIME, CommonUtil.getDateTimeStr(status.getUpdate_time()));
    db.update(NovelTable.Table_NovelDownRecord, values, NovelTable.NovelDownRecord.NOVEL_ID + " = ?",
        new String[]{String.valueOf(status.getNovelId())});
  }

  public List<NovelDownInfo> getNovelsNotDown() {
    List<NovelDownInfo> list = new ArrayList<>();
    SQLiteDatabase db = instance.getReadableDatabase();
    Cursor cursor = db.rawQuery("select r.*, d.down_status from novel_read_record r left join"
        + " novel_down_record d on r.novel_id = d.novel_id where d.down_status != ? order by r.update_time", new String[]{CommonUtil.DOWN_STATUS.SUCCESS});
    if (cursor != null && cursor.moveToFirst()) {
      do {
        int novelId = getIntColumn(cursor, NovelTable.NovelReadRecord.NOVEL_ID);
        if (novelId == CommonUtil.NONE_INT_DATA) {
          continue;
        }
        String novelName = getStringColumn(cursor, NovelTable.NovelReadRecord.NOVEL_NAME);
        String downStatus = getStringColumn(cursor, NovelTable.NovelDownRecord.DOWN_STATUS);
        String createTime = getStringColumn(cursor, NovelTable.NovelDownRecord.CREATE_TIME);
        String updateTime = getStringColumn(cursor, NovelTable.NovelDownRecord.UPDATE_TIME);

        NovelDownStatus status = new NovelDownStatus();
        status.setNovelId(novelId);
        status.setDownStatus(TextUtils.isEmpty(downStatus) ? CommonUtil.DOWN_STATUS.DEFAULT : downStatus);
        status.setCreate_time(CommonUtil.getDateTime(createTime));
        status.setUpdate_time(CommonUtil.getDateTime(updateTime));
        NovelDownInfo record = new NovelDownInfo();
        record.setNovelDownStatus(status);
        record.setNovelName(novelName);
        list.add(record);
      } while (cursor.moveToNext());
    }
    return list;
  }

  public NovelDownStatus getNovelDownStatusById(int novelId) {
    SQLiteDatabase db = instance.getReadableDatabase();
    Cursor cursor = db.query(NovelTable.Table_NovelDownRecord, null, NovelTable.NovelDownRecord.NOVEL_ID + " = ?", new String[]{String.valueOf(novelId)}, null, null, null);
    if (cursor != null && cursor.moveToFirst()) {
      NovelDownStatus status = new NovelDownStatus();
      status.setNovelId(novelId);
      String createTime = getStringColumn(cursor, NovelTable.NovelDownRecord.CREATE_TIME);
      String updateTime = getStringColumn(cursor, NovelTable.NovelDownRecord.UPDATE_TIME);
      String downStatus = getStringColumn(cursor, NovelTable.NovelDownRecord.DOWN_STATUS);
      status.setCreate_time(CommonUtil.getDateTime(createTime));
      status.setUpdate_time(CommonUtil.getDateTime(updateTime));
      status.setDownStatus(TextUtils.isEmpty(downStatus) ? CommonUtil.DOWN_STATUS.DEFAULT : downStatus);
      return status;
    }
    return null;
  }

  public boolean removeDownRecord(int novelId) {
    SQLiteDatabase db = instance.getWritableDatabase();
    int rowCount = db.delete(NovelTable.Table_NovelDownRecord, NovelTable.NovelDownRecord.NOVEL_ID + " = ?", new String[]{String.valueOf(novelId)});
    return rowCount != 0;
  }
}
